﻿Public Class frmSearchInfo
    Dim Mysql As New ClsSQLhelper
    Dim Strsql As String
    Dim Key_CID As String
    Dim Key_HN As String
    Dim Key_PID As String
    Dim Key_FULLNAME As String
    Dim DT_patient As New DataTable
    Dim DT_ptcardno As New DataTable
    Dim DT_person As New DataTable

    Dim Person As New ClsPerson
    Dim Patient As New ClsPerson

#Region "Function"
    Sub Analysis_Data()
        'Analysis
        'Check Match CID
        Try
            If Person.CID <> Patient.CID Then

                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : หมายเลขบัตรประชาชนไม่ตรงกัน"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : หมายเลขบัตรประชาชนไม่ตรงกัน"
                End If

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


        'Check Match PName
        Try
            If Person.Pname <> Patient.Pname Then

                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : คำนำหน้าไม่ตรงกัน"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : คำนำหน้าไม่ตรงกัน"
                End If

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try



        'Check Match FirstName
        Try
            If Person.Fname <> Patient.Fname Then

                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : ชื่อไม่ตรงกัน"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : ชื่อไม่ตรงกัน"
                End If

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Check Match LName
        Try
            If Person.Lname <> Patient.Lname Then

                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : นามสกุลไม่ตรงกัน"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : นามสกุลไม่ตรงกัน"
                End If

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Duplicate(CID)
        Dim StrDupPerson As String
        StrDupPerson = "Select count(*) as CC from person where  patient_hn = '" & Me.TxtHN.Text & "' and cid = '" & Me.TxtCID.Text & "' and concat(fname,' ',lname) = '" & Me.TxtFullname.Text & "'"
        If Mysql.MySQLExecuteScalar(StrDupPerson, "hos") > 1 Then
            If Me.TxtDesc.Text = "" Then
                Me.TxtDesc.Text = "คำเตือน : มีข้อมูลที่มีความคล้ายกันมาก แนะนำให้รวม PID ใน Person"
            Else
                Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : มีข้อมูลที่มีความคล้ายกันมาก แนะนำให้รวม PID ใน Person"
            End If
        End If


        'Check Newborn
        If (InStr(Me.TxtFullname.Text, "(", CompareMethod.Text) = 1) And (Me.TxtFullname.Text <> "") Then
            If Me.TxtDesc.Text = "" Then
                Me.TxtDesc.Text = "คำเตือน : ข้อมูลนี้อาจเป็นของเด็กแรกเกิด"
            Else
                Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : ข้อมูลนี้อาจเป็นของเด็กแรกเกิด"
            End If
        End If

        Me.TxtDesc.Text = ""
      

        Try
            'Nationality
            If Me.TxtNationality.Text = "00" Then
                Me.TxtNationality.BackColor = Color.Red
                Me.TxtNationality.ForeColor = Color.White

                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : ไม่ได้ระบุสัญชาติ"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : ไม่ได้ระบุสัญชาติ"
                End If
            Else
                Me.TxtNationality.BackColor = Color.White
                Me.TxtNationality.ForeColor = Color.Black

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Education
        Try
            If Me.TxtEducate.Text <> "" Then
                Me.TxtEducate.BackColor = Color.White
                Me.TxtEducationName.BackColor = Color.White

            Else
                Me.TxtEducate.BackColor = Color.Red
                Me.TxtEducationName.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : การศึกษา เป็นค่าว่าง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : การศึกษา เป็นค่าว่าง"
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Sex
        Try
            If Me.TxtSex.Text <> "" Then
                Me.TxtSex.BackColor = Color.White
                Me.TxtSexName.BackColor = Color.White
            Else

                Me.TxtSex.BackColor = Color.Red
                Me.TxtSexName.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "ไม่ได้ระบุเพศ"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "ไม่ได้ระบุเพศ"
                End If

            End If




        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'MStatus
        Try


            'Dim StrSQLMarryStatus As String = "select name from marrystatus where code = " & Me.TxtMStatus.Text
            'Me.TxtMStatusname.Text = Mysql.MySQLExecuteScalar(StrSQLMarryStatus, "hos")
            If Me.TxtMStatus.Text <> "" Then
                Me.TxtMStatus.BackColor = Color.White
                Me.TxtMStatusname.BackColor = Color.White
            Else
                Me.TxtMStatus.BackColor = Color.Red
                Me.TxtMStatusname.BackColor = Color.Red

                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "ข้อมูลสถาณะสมรสไม่ถูกต้อง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "ข้อมูลสถาณะสมรสไม่ถูกต้อง"
                End If

            End If



        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Occupation
        Try
            If Me.TxtOccupation.Text <> "" Then
                Me.TxtOccupation.BackColor = Color.White
                Me.TxtOccupationname.BackColor = Color.White
            Else
                Me.TxtMStatus.BackColor = Color.Red
                Me.TxtMStatusname.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "ข้อมูลอาชีพไม่ถูกต้อง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "ข้อมูลอาชีพไม่ถูกต้อง"
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Type Area
        Try
            If Me.TxtTypeArea.Text <> "" Then
                Me.TxtTypeArea.BackColor = Color.White
                Me.TxtTypeAreaName.BackColor = Color.White
              
            Else
                Me.TxtTypeArea.BackColor = Color.Red
                Me.TxtTypeAreaName.BackColor = Color.Red
                Me.TxtTypeAreaName.Text = ""
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "Type Area เป็นค่าว่าง"
                    'Else
                    '    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "Type Area เป็นค่าว่าง"
                End If
            End If

            ' Dim StrSQLTypeArea As String = "select house_regist_type_name from house_regist_type where house_regist_type_id = " & Me.TxtTypeArea.Text
            ' Me.TxtTypeAreaName.Text = Mysql.MySQLExecuteScalar(StrSQLTypeArea, "hos")




        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Try
            Dim StrVn_Stat_sql As String
            StrVn_Stat_sql = "select vstdate  from vn_stat where hn ='" & Me.TxtHN.Text & "' order by vstdate desc limit 1"


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Try
            'Find Correct Row
            Dim i As Integer

            If GridVNStat.RowCount > 0 Then
                With Me.DataGridView1

                    For i = 0 To .RowCount - 1
                        If .Rows(i).Cells("hn").Value.ToString = GridVNStat.Rows(0).Cells("hn").Value.ToString Then
                            .Rows(i).DefaultCellStyle.BackColor = Color.Aqua
                        Else
                            .Rows(i).DefaultCellStyle.BackColor = Color.White
                        End If
                    Next


                End With


                With Me.DataGridView2
                    For i = 0 To .RowCount - 1
                        If .Rows(i).Cells("patient_hn").Value.ToString = GridVNStat.Rows(0).Cells("hn").Value.ToString Then
                            .Rows(i).DefaultCellStyle.BackColor = Color.Aqua
                        Else
                            .Rows(i).DefaultCellStyle.BackColor = Color.White
                        End If
                        Exit For
                    Next
                End With
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'BloodGroup
        Try
            If Me.TxtBloodGroup.Text <> "" Then
                Me.TxtBloodGroup.BackColor = Color.White
                Me.TxtBloodGroup.BackColor = Color.White

            Else
                Me.TxtBloodGroup.BackColor = Color.Red
                Me.TxtBloodGroup.BackColor = Color.Red
                Me.TxtBloodGroup.Text = ""
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "BloodGroup เป็นค่าว่าง"
                    'Else
                    '    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "Type Area เป็นค่าว่าง"
                End If
            End If

            ' Dim StrSQLTypeArea As String = "select house_regist_type_name from house_regist_type where house_regist_type_id = " & Me.TxtTypeArea.Text
            ' Me.TxtTypeAreaName.Text = Mysql.MySQLExecuteScalar(StrSQLTypeArea, "hos")




        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Sub Submit_Search()
        'Patient
        Try
            Me.TxtDesc.Text = ""
            Key_CID = ""
            Key_HN = ""
            Dim ScarlarSQL As String = ""
            Strsql = "Select hn,cid,pname,fname,lname,birthday,sex,bloodgrp,fathername,mathername,last_visit,nationality,country,type_area,occupation,marrystatus,educate,last_update from patient where"
            Dim StrCretiria As String = ""

            'Check Name
            If (InStr(Me.TxtCriteria.Text, " ", CompareMethod.Text) > 0) And (Me.TxtCriteria.Text.Trim <> "") Then
                Key_FULLNAME = Me.TxtCriteria.Text
                StrCretiria = "  concat(fname,' ',lname)  = '" & Me.TxtCriteria.Text & "'"
                GoTo DrawGrid
            End If

            'Check CID
            If Len(Me.TxtCriteria.Text) = 13 Then
                Dim StrFindHNSql As String
                Key_CID = Me.TxtCriteria.Text
                StrCretiria = "  CID = '" & Me.TxtCriteria.Text & "'"

                StrFindHNSql = "select hn from patient where cid = '" & Me.TxtCriteria.Text & "'"
                Key_HN = Mysql.MySQLExecuteScalar(StrFindHNSql, "hos")

                If Key_HN = Nothing Then
                    StrFindHNSql = "select patient_hn from person where cid = '" & Me.TxtCriteria.Text & "'"
                    Key_HN = Mysql.MySQLExecuteScalar(StrFindHNSql, "hos")
                    StrCretiria = StrCretiria & " or HN = '" & Key_HN & "' "
                    TxtDesc.Text = "ไม่พบข้อมูล  CID นี้ ใน Patient ระบบจะค้นหาโดยใช้ hn จาก person แทน"
                    MsgBox("ไม่พบข้อมูล  CID นี้ ใน Patient ระบบจะลองค้นหาโดยใช้ hn จาก person แทน", MsgBoxStyle.Exclamation, "คำเตือน")

                End If



                GoTo DrawGrid
            End If

            'Check HN
            Dim HNMax As String = System.Configuration.ConfigurationSettings.AppSettings.Get("HN_Max_Length")
            'If Len(Me.TxtCriteria.Text) > HNMax Then
            '    MsgBox("Wrong HN Format", MsgBoxStyle.Critical)
            'End If

            If Len(Me.TxtCriteria.Text) = HNMax Then
                StrCretiria = " HN = '" & Me.TxtCriteria.Text & "'"
                Key_HN = Me.TxtCriteria.Text
                GoTo DrawGrid
            End If

            'Check PID

            'Dim StrFindCIDSql As String
            'Key_PID = Me.TxtCriteria.Text
            'StrFindCIDSql = "select cid from person where person_id = '" & Me.TxtCriteria.Text & "'"
            'Key_CID = Mysql.MySQLExecuteScalar(StrFindCIDSql, "hos")
            'If Key_CID = Nothing Then
            '    StrFindCIDSql = "select hn from person where person_id = '" & Me.TxtCriteria.Text & "'"
            '    Key_HN = Mysql.MySQLExecuteScalar(StrFindCIDSql, "hos")
            'End If
            'If Key_HN = Nothing Then GoTo DrawGrid

            Dim StrPersonsql As String
            StrPersonsql = "select person_id,patient_hn,cid,pname,fname,lname from person where person_id = '" & Me.TxtCriteria.Text & "' "
            Dim DT_Search_Person As New DataTable
            DT_Search_Person = Mysql.GetMYSQLDataTable(StrPersonsql, "person", True)

            If DT_Search_Person.Rows.Count = 0 Then

                MsgBox("เงื่อนไขไม่ถูกต้อง ไม่พบความเชื่อมโยง", MsgBoxStyle.Exclamation, "คำเตือน")
                Me.DataGridView3.DataSource = Nothing
                Me.DataGridView2.DataSource = Nothing
                Me.DataGridView1.DataSource = Nothing
                Exit Sub
            Else
                ' MyCID = DT_Search_Person.Rows(0).Item("cid").ToString
                Key_CID = DT_Search_Person.Rows(0).Item("cid").ToString
                Key_HN = DT_Search_Person.Rows(0).Item("patient_hn").ToString
                Key_PID = DT_Search_Person.Rows(0).Item("person_id").ToString
                Key_FULLNAME = DT_Search_Person.Rows(0).Item("fname").ToString & " " & DT_Search_Person.Rows(0).Item("lname").ToString
            End If

            If Key_CID <> "" And Key_HN <> "" Then StrCretiria = " CID = '" & Key_CID & "' or  HN = '" & Key_HN & "'"
            If Key_CID = "" And Key_HN <> "" Then StrCretiria = " HN = '" & Key_HN & "'"
            If Key_CID <> "" And Key_HN = "" Then StrCretiria = " CID = '" & Key_CID & "'"

            GoTo DrawGrid

DrawGrid:

            If (Me.ChkShowDupName.Checked = True) And (Key_FULLNAME <> "") Then

                If StrCretiria = "" Then
                    StrCretiria = Replace(" or concat(fname,' ',lname)  = '" & Key_FULLNAME & "' ", "or", " ")
                Else
                    StrCretiria = StrCretiria & " or concat(fname,' ',lname)  = '" & Key_FULLNAME & "' "
                End If


            End If


            If StrCretiria = "" Then
                Strsql = Replace(Strsql, "where", " limit 0")
            Else
                Strsql = Strsql & StrCretiria
            End If

            DT_patient = Mysql.GetMYSQLDataTable(Strsql, "patient", True)

            With DataGridView1
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_patient ' DSOPPP.Tables("m_person")
                'If .RowCount = 0 Then
                '    TxtDesc.Text = "ไม่พบข้อมูลใน Patient"
                '    MsgBox("ไม่พบข้อมูลใน Patient", MsgBoxStyle.Exclamation, "คำเตือน")
                'Else


                '  End If
            End With

            With Patient
                If DataGridView1.RowCount <> 0 Then
                    .CID = Me.DataGridView1.Rows(0).Cells("CID").Value.ToString
                    .HN = Me.DataGridView1.Rows(0).Cells("HN").Value.ToString
                    .Pname = Me.DataGridView1.Rows(0).Cells("pname").Value.ToString
                    .Fname = Me.DataGridView1.Rows(0).Cells("fname").Value.ToString
                    .Lname = Me.DataGridView1.Rows(0).Cells("lname").Value.ToString
                    .BirthDay = Me.DataGridView1.Rows(0).Cells("birthday").Value.ToString
                End If
            End With


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Peson
        Try


            Strsql = "select person_id,patient_hn,cid,pname,fname,lname,sex,blood_group,nationality,education,occupation,marrystatus,birthdate,last_update,patient_link,register_conflict,house_regist_type_id from person where "
            Dim StrSQLDuplicate As String = ""


            If (Me.ChkShowDupName.Checked = True) And (Key_FULLNAME <> "") Then
                StrSQLDuplicate = StrSQLDuplicate & " or concat(fname,' ',lname)  = '" & Key_FULLNAME & "' "
                If StrSQLDuplicate = "" Then StrSQLDuplicate = Replace(StrSQLDuplicate, "or", " ")
            End If



            If Key_CID <> "" Then
                Strsql = Strsql & " cid = '" & Key_CID & "'" & StrSQLDuplicate & " order by cid"
            ElseIf (Key_HN <> "") Then
                Strsql = Strsql & " patient_hn = '" & Key_HN & "'" & StrSQLDuplicate & " order by patient_hn"
            ElseIf (Key_PID <> "") Then
                Strsql = Strsql & " person_id = '" & Key_PID & "'" & StrSQLDuplicate & " order by person_id"
            End If



            DT_person = Mysql.GetMYSQLDataTable(Strsql, "person", True)
            With DataGridView2

                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_person ' DSOPPP.Tables("m_person")


            End With
            With Person
                If DataGridView2.RowCount <> 0 Then
                    .CID = Me.DataGridView2.Rows(0).Cells("CID").Value.ToString
                    .HN = Me.DataGridView2.Rows(0).Cells("patient_hn").Value.ToString
                    .Pname = Me.DataGridView2.Rows(0).Cells("pname").Value.ToString
                    .Fname = Me.DataGridView2.Rows(0).Cells("fname").Value.ToString
                    .Lname = Me.DataGridView2.Rows(0).Cells("lname").Value.ToString
                    .BirthDay = Me.DataGridView2.Rows(0).Cells("birthdate").Value.ToString
                End If
            End With

            'Analysis Table Person


            'Dim StrDupPatient As String
            'StrDupPatient = "Select count(*) as CC from patient where cid = '" & Me.TxtCID.Text & "' and concat(fname,' ',lname) = '" & Me.TxtFullname.Text & "'"
            'If Mysql.MySQLExecuteScalar(StrDupPatient, "hos") > 1 Then
            '    Me.TxtDesc.Text = "มีข้อมูลที่มีความคล้ายกันมาก แนะนำให้รวม PID ใน Person"
            'End If
          




            With Me.DataGridView2
                If .RowCount = 0 Then
                    If Me.DataGridView1.RowCount = 0 Then Exit Sub
                    lblInformationTitle.Text = "ข้อมูล Patient"
                    Me.TxtFullname.Text = DataGridView1.Rows(0).Cells("fname").Value.ToString.Trim & " " & DataGridView1.Rows(0).Cells("lname").Value.ToString.Trim
                    Me.TxtCID.Text = DataGridView1.Rows(0).Cells("cid").Value.ToString.Trim
                    Me.TxtHN.Text = DataGridView1.Rows(0).Cells("hn").Value.ToString.Trim
                    Me.TxtNationality.Text = DataGridView1.Rows(0).Cells("nationality").Value.ToString.Trim
                    Me.TxtSex.Text = DataGridView1.Rows(0).Cells("sex").Value.ToString.Trim
                    Me.TxtBirthday.Text = DataGridView1.Rows(0).Cells("birthday").Value.ToString.Trim
                    Me.TxtTypeArea.Text = DataGridView1.Rows(0).Cells("type_area").Value.ToString.Trim
                    Me.TxtOccupation.Text = DataGridView1.Rows(0).Cells("occupation").Value.ToString.Trim
                    Me.TxtMStatus.Text = DataGridView1.Rows(0).Cells("marrystatus").Value.ToString.Trim
                    Me.TxtLastVisit.Text = DataGridView1.Rows(0).Cells("last_visit").Value.ToString.Trim
                    Me.TxtLastUpdate.Text = DataGridView1.Rows(0).Cells("last_update").Value.ToString.Trim
                    Me.TxtEducate.Text = DataGridView1.Rows(0).Cells("educate").Value.ToString.Trim
                    Key_CID = DataGridView1.Rows(0).Cells(1).Value.ToString.Trim
                    Key_HN = DataGridView1.Rows(0).Cells(0).Value.ToString.Trim
                Else


                    lblInformationTitle.Text = "ข้อมูล Person"
                    Me.TxtFullname.Text = .Rows(0).Cells(4).Value.ToString.Trim & " " & .Rows(0).Cells(5).Value.ToString.Trim
                    If (InStr(Me.TxtFullname.Text, "(", CompareMethod.Text) = 1) And (Me.TxtFullname.Text <> "") Then
                        Me.TxtDesc.Text = "คำเตือน : ข้อมูลนี้อาจเป็นของเด็กแรกเกิด"
                    End If
                    Me.TxtCID.Text = .Rows(0).Cells("cid").Value.ToString.Trim
                    Me.TxtHN.Text = .Rows(0).Cells("patient_hn").Value.ToString.Trim
                    Me.TxtNationality.Text = .Rows(0).Cells("nationality").Value.ToString.Trim()
                    Me.TxtSex.Text = .Rows(0).Cells("sex").Value.ToString.Trim
                    Me.TxtBirthday.Text = .Rows(0).Cells("birthdate").Value.ToString.Trim
                    Me.TxtTypeArea.Text = .Rows(0).Cells("house_regist_type_id").Value.ToString.Trim
                    Me.TxtOccupation.Text = .Rows(0).Cells("occupation").Value.ToString.Trim
                    Me.TxtMStatus.Text = .Rows(0).Cells("marrystatus").Value.ToString.Trim
                    Me.TxtLastVisit.Text = ""
                    Me.TxtLastUpdate.Text = .Rows(0).Cells("last_update").Value.ToString.Trim
                    Me.TxtEducate.Text = .Rows(0).Cells("education").Value.ToString.Trim
                    Me.TxtPID.Text = .Rows(0).Cells("person_id").Value.ToString.Trim
                    If Me.TxtPID.Text <> "" Then Me.TxtPID.BackColor = Color.White
                    'GET PID
                    Me.TxtPID.Text = DataGridView2.Rows(0).Cells("person_id").Value.ToString.Trim
                    'Patient Link
                    If DT_person.Rows(0).Item("patient_link").ToString = "Y" Then
                        Me.ChkPatientLink.Checked = True
                    Else
                        Me.ChkPatientLink.Checked = False
                    End If

                    'Register Conflict
                    If DT_person.Rows(0).Item("register_conflict").ToString = "Y" Then
                        Me.ChkRegConflict.Checked = True
                    Else
                        Me.ChkRegConflict.Checked = False
                    End If

                End If

            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'VnStat
        Try

            Dim StrVn_Stat_sql As String
            StrVn_Stat_sql = "select vn,hn,cid,vstdate as dateserv,income as price,rcpt_money as paid,pdx,dx0,dx1,dx2,dx3,dx4,dx5  "
            StrVn_Stat_sql = StrVn_Stat_sql & " from vn_stat  where hn ='" & Me.TxtHN.Text & "' "
            StrVn_Stat_sql = StrVn_Stat_sql & " and ym='" & Val(Me.TxtYear.Text) - 543 & "-" & Me.TxtMonth.Text & "' "
            If Me.TxtDateServ.Text <> "" And Me.TxtDateServ.Text.Length = 8 And Me.ChkShowAllVisit.Checked = False Then
                StrVn_Stat_sql = StrVn_Stat_sql & " and vstdate = '" & Mid(Me.TxtDateServ.Text, 1, 4) & "-" & Mid(Me.TxtDateServ.Text, 5, 2) & "-" & Mid(Me.TxtDateServ.Text, 7, 2) & "'"
            End If
            Dim DT_VNStat As New DataTable
            DT_VNStat = Mysql.GetMYSQLDataTable(StrVn_Stat_sql, "vn_stat", True)
            With GridVNStat
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_VNStat
            End With
            '  DT_VNStat.Dispose()
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try

      
        'PtCardno
        Try
            Strsql = "select * from ptcardno where  "
            If Key_CID <> "" Then
                Strsql = Strsql & " cardno = '" & Mid(Key_CID, 1, 1) & "-" & Mid(Key_CID, 2, 4) & "-" & Mid(Key_CID, 6, 5) & "-" & Mid(Key_CID, 11, 2) & "-" & Mid(Key_CID, 13, 1) & "' "
            Else
                Strsql = Strsql & " hn = '" & Key_HN & "'"
            End If


            DT_ptcardno = Mysql.GetMYSQLDataTable(Strsql, "ptcardno", True)
            With DataGridView3
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_ptcardno ' DSOPPP.Tables("m_person")
            End With
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try



        'MsgBox("ดึงข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)


    End Sub

#End Region
    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click
        Submit_Search()
        Analysis_Data()
    End Sub

    Private Sub TxtCriteria_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TxtCriteria.KeyDown
        If e.KeyCode = Keys.Enter Then
            BtnSearch_Click(sender, e)
        ElseIf e.KeyCode = Keys.Escape Then
            Me.Dispose()
        End If
    End Sub
    Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click
        Me.Dispose()
    End Sub
    Private Sub BtnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim MyROwNumber As Integer = Me.DataGridView3.RowCount + 1
        If (Me.TxtCID.Text <> "") And (Me.TxtHN.Text <> "") Then
            Strsql = "Insert into ptcardno (hn,cardtype,cardno) select hn,'01' as cardtype,concat(left(cid,1),'-',mid(cid,2,4),'-',mid(cid,5,5),'-',mid(cid,11,2),'-',right(cid,1)) as cardno from patient where  CID = '" & Me.TxtCID.Text & "'"
            If MyROwNumber > 0 Then
                For Each DR_User In DT_ptcardno.Select("cardno = '" & Pn_Framework.FormatCID(Me.TxtCID.Text) & "'")
                    Dim CorrectCardType As String = DR_User.Item("cardtype").ToString()
                    MsgBox("CID ที่ถูกต้อง อยู่ที่ CardType = " & CorrectCardType, MsgBoxStyle.Information)
                    Exit For
                Next
            End If

        ElseIf (Me.TxtCID.Text = "0000000000000") And (Me.TxtHN.Text <> "") Then

            If MyROwNumber > 0 Then
                For Each DR_User In DT_ptcardno.Select("cardno = '0-0000-00000-00-0'")
                    Dim CorrectCardType As String = DR_User.Item("cardtype").ToString()
                    MsgBox("CID ที่ถูกต้อง อยู่ที่ CardType = " & CorrectCardType, MsgBoxStyle.Information)
                    Exit For
                Next
            End If
        ElseIf (Me.TxtCID.Text = "") And (Me.TxtHN.Text <> "") Then
            Strsql = "Select nationality from patient where hn = '" & Me.TxtHN.Text & "'"
            Dim MyNationality As String = Mysql.MySQLExecuteScalar(Strsql, "hos")
            Strsql = "Insert into ptcardno (hn,cardtype,cardno) values ('" & Me.TxtHN.Text & "','01','0-0000-00000-00-0')"

        End If


    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            Me.lblDesc.Text = ""
            With Me.DataGridView1
                If .RowCount = 0 Then Exit Sub
                lblInformationTitle.Text = "ข้อมูล Patient"

                Me.TxtFullname.Text = .Rows(e.RowIndex).Cells("fname").Value.ToString & " " & DataGridView1.Rows(e.RowIndex).Cells("lname").Value.ToString
                Me.TxtCID.Text = .Rows(e.RowIndex).Cells("cid").Value.ToString

                Me.TxtHN.Text = .Rows(e.RowIndex).Cells("hn").Value.ToString
                Me.TxtNationality.Text = .Rows(e.RowIndex).Cells("nationality").Value.ToString
                Me.TxtSex.Text = .Rows(e.RowIndex).Cells("sex").Value.ToString
                Me.TxtBirthday.Text = .Rows(e.RowIndex).Cells("birthday").Value.ToString
                Me.TxtTypeArea.Text = .Rows(e.RowIndex).Cells("type_area").Value.ToString
                Me.TxtOccupation.Text = .Rows(e.RowIndex).Cells("occupation").Value.ToString
                Me.TxtMStatus.Text = .Rows(e.RowIndex).Cells("marrystatus").Value.ToString
                Me.TxtLastVisit.Text = .Rows(e.RowIndex).Cells("last_visit").Value.ToString
                Me.TxtLastUpdate.Text = .Rows(e.RowIndex).Cells("last_update").Value.ToString
                Me.TxtEducate.Text = DataGridView1.Rows(0).Cells("educate").Value.ToString
                Me.TxtBloodGroup.Text = DataGridView1.Rows(0).Cells("bloodgrp").Value.ToString
            End With
            With Patient
                .CID = Me.DataGridView1.Rows(0).Cells("CID").Value.ToString
                .HN = Me.DataGridView1.Rows(0).Cells("HN").Value.ToString
                .Pname = Me.DataGridView1.Rows(0).Cells("pname").Value.ToString
                .Fname = Me.DataGridView1.Rows(0).Cells("fname").Value.ToString
                .Lname = Me.DataGridView1.Rows(0).Cells("lname").Value.ToString
                .BirthDay = Me.DataGridView1.Rows(0).Cells("birthday").Value.ToString

            End With
            Analysis_Data()



        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'vn_stat
        Try

            Dim StrVn_Stat_sql As String
            StrVn_Stat_sql = "select vn,hn,cid,vstdate as dateserv,income as price,rcpt_money as paid,pdx,dx0,dx1,dx2,dx3,dx4,dx5  "
            StrVn_Stat_sql = StrVn_Stat_sql & " from vn_stat  where hn ='" & Me.TxtHN.Text & "' "
            StrVn_Stat_sql = StrVn_Stat_sql & " and ym='" & Val(Me.TxtYear.Text) - 543 & "-" & Me.TxtMonth.Text & "' "
            If Me.TxtDateServ.Text <> "" And Me.TxtDateServ.Text.Length = 8 And Me.ChkShowAllVisit.Checked = False Then
                StrVn_Stat_sql = StrVn_Stat_sql & " and vstdate = '" & Mid(Me.TxtDateServ.Text, 1, 4) & "-" & Mid(Me.TxtDateServ.Text, 5, 2) & "-" & Mid(Me.TxtDateServ.Text, 7, 2) & "'"
            End If
            Dim DT_VNStat As New DataTable
            DT_VNStat = Mysql.GetMYSQLDataTable(StrVn_Stat_sql, "vn_stat", True)
            With GridVNStat
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_VNStat

            End With
            If GridVNStat.RowCount > 0 Then
                With Me.DataGridView1
                    For i = 0 To .RowCount - 1
                        If .Rows(i).Cells("hn").Value.ToString = GridVNStat.Rows(0).Cells("hn").Value.ToString Then
                            .Rows(i).DefaultCellStyle.BackColor = Color.Aqua

                        Else
                            .Rows(i).DefaultCellStyle.BackColor = Color.White
                        End If
                    Next
                End With

            End If
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub DataGridView2_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick

        Me.lblDesc.Text = ""
        Try
            With Me.DataGridView2
                If .RowCount = 0 Then Exit Sub
                lblInformationTitle.Text = "ข้อมูล Person"
                Me.TxtFullname.Text = .Rows(e.RowIndex).Cells(4).Value.ToString & " " & .Rows(e.RowIndex).Cells(5).Value.ToString
                Me.TxtCID.Text = .Rows(e.RowIndex).Cells("cid").Value.ToString
                Me.TxtHN.Text = .Rows(e.RowIndex).Cells("patient_hn").Value.ToString
                Me.TxtNationality.Text = .Rows(e.RowIndex).Cells("nationality").Value.ToString
                Me.TxtSex.Text = .Rows(e.RowIndex).Cells("sex").Value.ToString
                Me.TxtBirthday.Text = .Rows(e.RowIndex).Cells("birthdate").Value.ToString()
                Me.TxtTypeArea.Text = .Rows(e.RowIndex).Cells("house_regist_type_id").Value.ToString
                Me.TxtOccupation.Text = .Rows(e.RowIndex).Cells("occupation").Value.ToString
                Me.TxtMStatus.Text = .Rows(e.RowIndex).Cells("marrystatus").Value.ToString
                Me.TxtLastVisit.Text = ""
                Me.TxtLastUpdate.Text = .Rows(e.RowIndex).Cells("last_update").Value.ToString
                Me.TxtEducate.Text = .Rows(0).Cells("education").Value.ToString
                Me.TxtPID.Text = .Rows(0).Cells("person_id").Value.ToString
                Me.TxtBloodGroup.Text = .Rows(0).Cells("blood_group").Value.ToString
                'Patient Link
                If DT_person.Rows(e.RowIndex).Item("patient_link").ToString = "Y" Then
                    Me.ChkPatientLink.Checked = True
                Else
                    Me.ChkPatientLink.Checked = False
                End If

                'Register Conflict
                If DT_person.Rows(e.RowIndex).Item("register_conflict").ToString = "Y" Then
                    Me.ChkRegConflict.Checked = True
                Else
                    Me.ChkRegConflict.Checked = False
                End If

                With Person
                    .CID = Me.DataGridView1.Rows(0).Cells("CID").Value.ToString
                    .HN = Me.DataGridView1.Rows(0).Cells("HN").Value.ToString
                    .Pname = Me.DataGridView1.Rows(0).Cells("pname").Value.ToString
                    .Fname = Me.DataGridView1.Rows(0).Cells("fname").Value.ToString
                    .Lname = Me.DataGridView1.Rows(0).Cells("lname").Value.ToString
                    .BirthDay = Me.DataGridView1.Rows(0).Cells("birthday").Value.ToString

                End With

                Analysis_Data()
                'CID
                Try
                    If Me.DataGridView2.Rows(e.RowIndex).Cells("CID").Value.ToString <> Me.DataGridView1.Rows(0).Cells("CID").Value.ToString Then

                        If Me.TxtDesc.Text = "" Then
                            Me.TxtDesc.Text = "คำเตือน : หมายเลขบัตรประชาชนไม่ตรงกัน"
                        Else
                            Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน : หมายเลขบัตรประชาชนไม่ตรงกัน"
                        End If

                    End If
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'vn_stat
        Try

            Dim StrVn_Stat_sql As String
            StrVn_Stat_sql = "select vn,hn,cid,vstdate as dateserv,income as price,rcpt_money as paid,pdx,dx0,dx1,dx2,dx3,dx4,dx5  "
            StrVn_Stat_sql = StrVn_Stat_sql & " from vn_stat  where hn ='" & Me.TxtHN.Text & "' "
            StrVn_Stat_sql = StrVn_Stat_sql & " and ym='" & Val(Me.TxtYear.Text) - 543 & "-" & Val(Me.TxtMonth.Text) & "' "
            If Me.TxtDateServ.Text <> "" And Me.TxtDateServ.Text.Length = 8 And Me.ChkShowAllVisit.Checked = False Then
                StrVn_Stat_sql = StrVn_Stat_sql & " and vstdate = '" & Mid(Me.TxtDateServ.Text, 1, 4) & "-" & Mid(Me.TxtDateServ.Text, 5, 2) & "-" & Mid(Me.TxtDateServ.Text, 7, 2) & "'"
            End If
            Dim DT_VNStat As New DataTable
            DT_VNStat = Mysql.GetMYSQLDataTable(StrVn_Stat_sql, "vn_stat", True)
            With GridVNStat
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_VNStat
            End With

            If GridVNStat.RowCount > 0 Then
                With Me.DataGridView2
                    For i = 0 To .RowCount - 1
                        If .Rows(i).Cells("patient_hn").Value.ToString = GridVNStat.Rows(0).Cells("hn").Value.ToString Then
                            .Rows(i).DefaultCellStyle.BackColor = Color.Aqua
                        Else
                            .Rows(i).DefaultCellStyle.BackColor = Color.White
                        End If
                        Exit For
                    Next
                End With
            End If
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub DataGridView3_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView3.CellClick

        Try
            With Me.DataGridView3
                If .RowCount = 0 Then Exit Sub
                'If InStr(Me.TxtFullname.Text, "(", CompareMethod.Text) = 1 Then
                '    lblDesc.Text = "คำเตือน : ข้อมูลนี้อาจเป็นของเด็กแรกเกิด"
                'End If
                ' Me.TxtFullname.Text = .Rows(0).Cells(3).Value.ToString.Trim & " " & .Rows(0).Cells(4).Value.ToString.Trim
                Me.TxtCID.Text = Replace(.Rows(e.RowIndex).Cells(2).Value.ToString.Trim, "-", "")
                Me.TxtHN.Text = .Rows(e.RowIndex).Cells(0).Value.ToString.Trim

            End With
        Catch ex As Exception
            '  MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub BtnFullname_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Clipboard.SetDataObject(Me.TxtFullname.Text)
    End Sub

    Private Sub TxtNationality_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtNationality.TextChanged
        Try
            If Me.TxtNationality.Text <> "" Then
                Me.TxtNationality.BackColor = Color.White
                Me.TxtNationalityName.BackColor = Color.White
                Dim StrSQLNationality As String = "select name from nationality where   nationality = '" & Me.TxtNationality.Text & "'"
                If Me.TxtNationality.Text = "99" Then
                    Me.TxtNationalityName.Text = "ไทย"
                Else
                    Me.TxtNationalityName.Text = Mysql.MySQLExecuteScalar(StrSQLNationality, "hos")
                End If
            Else
                Me.TxtNationality.BackColor = Color.Red
                Me.TxtNationalityName.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "สัญชาติ เป็นค่าว่าง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "สัญชาติ เป็นค่าว่าง"
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub


    Private Sub DataGridView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.Escape Then
            Me.Dispose()
        End If
    End Sub
    Private Sub DataGridView2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView2.KeyDown
        If e.KeyCode = Keys.Escape Then
            Me.Dispose()
        End If
    End Sub
    Private Sub DataGridView3_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView3.KeyDown
        If e.KeyCode = Keys.Escape Then
            Me.Dispose()
        End If
    End Sub


    Private Sub TxtBirthday_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtBirthday.TextChanged
        Try
            Dim StrBirthDay As String = Me.TxtBirthday.Text
            Dim DateBirthDay As Date

            If Me.TxtBirthday.Text = "" Then
                Me.TxtAge.Text = ""
                Me.TxtBirthday.BackColor = Color.Red
                Me.TxtAge.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "ไม่ได้ระบุอายุ"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "ไม่ได้ระบุอายุ"
                End If
            Else
                DateBirthDay = FormatDateTime(TxtBirthday.Text, DateFormat.ShortDate)
                Me.TxtBirthday.BackColor = Color.White
                Me.TxtAge.BackColor = Color.White
                Me.TxtAge.Text = Year(Now) - Year(DateBirthDay)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try



    End Sub

    Private Sub TxtAge_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtAge.TextChanged
        Try
            Me.TxtBirthday.BackColor = Color.White
            Me.TxtAge.BackColor = Color.White
            If Val(Me.TxtAge.Text) > 100 Then
                Me.TxtBirthday.BackColor = Color.Yellow
                Me.TxtAge.BackColor = Color.Yellow
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "คำเตือน : อายุ เกิน 100 ปี"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "คำเตือน :อายุ เกิน 100 ปี"
                End If
            Else

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub TxtCID_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtCID.TextChanged
        Try
            If TxtCID.Text <> "" Then
                Me.TxtCID.BackColor = Color.White
                Me.TxtCID.BackColor = Color.White
            Else

                Me.TxtCID.BackColor = Color.Red
                Me.TxtCID.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "CID เป็นค่าว่าง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "CID เป็นค่าว่าง"
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub TxtHN_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtHN.TextChanged
        Try
            If TxtHN.Text <> "" Then
                Me.TxtHN.BackColor = Color.White
                Me.TxtHN.BackColor = Color.White
            Else

                Me.TxtHN.BackColor = Color.Red
                Me.TxtHN.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "HN เป็นค่าว่าง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "HN เป็นค่าว่าง"
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub


    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub DataGridView2_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellContentClick

    End Sub

    Private Sub TxtTypeArea_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtTypeArea.TextChanged
        Select Case Me.TxtTypeArea.Text
            Case 0
                Me.TxtTypeAreaName.Text = "อยู่ในเขตรับผิดชอบแต่ไม่มีชื่อในทะเบียนบ้าน เช่น คนเร่ร่อน ไร้ที่อยู่"
            Case 1
                Me.TxtTypeAreaName.Text = "มีชื่อในทะเบียนบ้าน และอยู่อาศัยจริง"
            Case 2
                Me.TxtTypeAreaName.Text = "มีชื่อในทะเบียนบ้านแต่ไม่ได้อยู่อาศัย"
            Case 3
                Me.TxtTypeAreaName.Text = "ไม่มีชื่อในทะเบียนบ้านแต่มาอยู่อาศัย"
            Case 4
                Me.TxtTypeAreaName.Text = "บุคคลนอกเขต"
            Case Else
                Me.TxtTypeAreaName.Text = ""


        End Select
    End Sub

    Private Sub TxtMStatus_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtMStatus.TextChanged
        Select Me.TxtMStatus.Text


            Case 1
                Me.TxtMStatusname.Text = "โสด"
            Case 2
                Me.TxtMStatusname.Text = "คู่"
            Case 3
                Me.TxtMStatusname.Text = "ม่าย"
            Case 4
                Me.TxtMStatusname.Text = "หย่า"
            Case 5
                Me.TxtMStatusname.Text = "แยก"
            Case 6
                Me.TxtMStatusname.Text = "สมณะ"
            Case 9
                Me.TxtMStatusname.Text = "ไม่ทราบ"
            Case Else
                Me.TxtMStatusname.Text = ""

        End Select
    End Sub

    Private Sub TxtSex_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtSex.TextChanged
         Select Me.TxtSex.Text
            Case 1
                Me.TxtSexName.Text = "ชาย"
            Case 2
                Me.TxtSexName.Text = "หญิง"
        End Select
    End Sub

    Private Sub TxtOccupation_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtOccupation.TextChanged
        Try
            Dim StrSQLOccupation As String = "select name from occupation where occupation = '" & Me.TxtOccupation.Text & "'"
            Me.TxtOccupationname.Text = Mysql.MySQLExecuteScalar(StrSQLOccupation, "hos")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        
    End Sub

    Private Sub TxtEducate_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtEducate.TextChanged
        Try
            Me.TxtEducationName.Text = ""
            If Me.TxtEducate.Text = "" Then Exit Sub
            If Me.TxtEducate.Text = "0" Then
                Me.TxtEducationName.Text = "ไม่ทราบ"
                Exit Sub
            End If

            Dim StrSQLEducate As String = "select name from education where education =  " & Me.TxtEducate.Text
            Me.TxtEducationName.Text = Mysql.MySQLExecuteScalar(StrSQLEducate, "hos")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
       
    End Sub

    Private Sub frmSearchInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Analysis_Data()
    End Sub

    Private Sub BtnCIDTools_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCIDTools.Click

        With Me.DataGridView2
            'For i = 0 To .RowCount - 1
            '    If .Rows(i).Cells("patient_hn").Value.ToString = GridVNStat.Rows(0).Cells("hn").Value.ToString Then
            '        .Rows(i).DefaultCellStyle.BackColor = Color.Aqua
            '    Else
            '        .Rows(i).DefaultCellStyle.BackColor = Color.White
            '    End If
            '    Exit For
            'Next
            If .RowCount > 0 Then
                Dim My_HN As String
                Dim VnCount As Integer = 0
                Strsql = "Select patient_hn from person where person_id = '" & Me.TxtPID.Text & "'"
                My_HN = Mysql.MySQLExecuteScalar(Strsql, "hos")
                If My_HN <> "" Then
                    Strsql = "select count(*) as cc  "
                    Strsql = Strsql & " from vn_stat  where hn ='" & My_HN & "' "
                    Strsql = Strsql & " and ym='" & Val(Me.TxtYear.Text) - 543 & "-" & Val(Me.TxtMonth.Text) & "' "
                    '   If Me.TxtDateServ.Text <> "" And Me.TxtDateServ.Text.Length = 8 And Me.ChkShowAllVisit.Checked = False Then
                    ' Strsql = Strsql & " and vstdate = '" & Mid(Me.TxtDateServ.Text, 1, 4) & "-" & Mid(Me.TxtDateServ.Text, 5, 2) & "-" & Mid(Me.TxtDateServ.Text, 7, 2) & "'"
                    'End If
                    VnCount = Mysql.MySQLExecuteScalar(Strsql, "hos")
                    If VnCount = 0 Then
                        frmCIDTools.TxtPID.Text = Me.TxtPID.Text

                    End If
                End If


            End If
        End With
        With frmCIDTools
            If GridVNStat.RowCount <> 0 Then
                MsgBox("CID นี้ข้อมูลบริการ ไม่สามารถแก้ไขได้ กรุณาตรวจสอบ", MsgBoxStyle.Exclamation, "คำเตือน")
                Exit Sub
            End If

            .TxtCID.Text = Me.TxtCID.Text
            .TxtHN.Text = Me.TxtHN.Text

            .TxtName.Text = Me.TxtFullname.Text
            .ShowDialog()
        End With
    End Sub


    Private Sub TxtBloodGroup_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtBloodGroup.TextChanged
        Try
            If Me.TxtBloodGroup.Text <> "" Then
                Me.TxtBloodGroup.BackColor = Color.White
                Me.TxtBloodGroupName.BackColor = Color.White
                Dim StrSQLNationality As String = "select name from blood_group      where blood_id = " & Me.TxtBloodGroup.Text
               
            Else
                Me.TxtBloodGroup.BackColor = Color.Red
                Me.TxtBloodGroupName.BackColor = Color.Red
                If Me.TxtDesc.Text = "" Then
                    Me.TxtDesc.Text = "กรุ๊ปเลือด เป็นค่าว่าง"
                Else
                    Me.TxtDesc.Text = Me.TxtDesc.Text & vbCrLf & "กรุ๊ปเลือด เป็นค่าว่าง"
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class